﻿using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using EffiProz.Core.Results;
using EffiProz.Core;
using EffiProz.Core.Lib;
using EffiProz.Core.Navigators;

namespace EffiProz.Test
{
    /// <summary>
    /// Summary description for SequenceTest
    /// </summary>
    [TestClass]
    public class SequenceTest : BaseTest
    {
        public SequenceTest()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion


        [TestMethod]
        public void SequenceCreateTest()
        {
            Result resultIn;

            string table = TestHelper.GetNewTableName();
            string sql = "CREATE TABLE " + table + " (col1 int , col2 BIGINT); ";

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            sql = "CREATE SEQUENCE  Seq1" + table + "  AS INTEGER;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            sql = "CREATE SEQUENCE  Seq2" + table + "  AS BIGINT START WITH 101 INCREMENT BY 10;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);


            sql = "INSERT INTO " + table + " (col1, col2) VALUES( NEXT VALUE FOR Seq1" + table + " , NEXT VALUE FOR Seq2" +
                table + " );";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            int rcol1val = 0;
            int rcol2val = 101;
            string sql2 = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql2, 1);
            TestHelper.MatchSingleResultRow(resultIn, 0, rcol1val, (long)rcol2val);

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            rcol1val = 1;
            rcol2val = 111;
            sql2 = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql2, 2);
            TestHelper.MatchSingleResultRow(resultIn, 1, rcol1val, (long)rcol2val);

            //Alter Sequence
            sql2 = "ALTER SEQUENCE Seq2" + table + " RESTART WITH 200;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql2);

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            rcol1val = 2;
            rcol2val = 200;
            sql2 = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql2, 3);
            TestHelper.MatchSingleResultRow(resultIn, 2, rcol1val, (long)rcol2val);

            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);
            rcol1val = 3;
            rcol2val = 210;
            sql2 = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql2, 4);
            TestHelper.MatchSingleResultRow(resultIn, 3, rcol1val, (long)rcol2val);

            //Drop Sequence
            sql2 = "DROP SEQUENCE Seq2" + table + ";";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql2);
            resultIn = TestHelper.ExecuteUpdateSQL_Failure(sessionProxy, sql); //insert should fail

            //Sequence in Select statement
            sql2 = "SELECT NEXT VALUE FOR Seq1" + table + " FROM " + table + " ;";
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql2, 4);
            TestHelper.MatchSingleResultRow(resultIn, 0, 4);
            TestHelper.MatchSingleResultRow(resultIn, 1, 5);
            TestHelper.MatchSingleResultRow(resultIn, 2, 6);
            TestHelper.MatchSingleResultRow(resultIn, 3, 7);

            //Sequence in Update statement
            sql2 = "UPDATE " + table + " SET col1= NEXT VALUE FOR Seq1" + table + " ;";
            resultIn = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql2);
            sql = TestHelper.CreateStdSelect(table, 2);
            resultIn = TestHelper.ExecuteSelectSQL(sessionProxy, sql, 4);
            TestHelper.MatchSingleResultRow(resultIn, 0, 8);

        }
    }
}
